package com.robot.merchant.dao;

import com.robot.merchant.pojo.MerchantMember;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

/**
 * member数据访问接口
 *
 * @author lin
 */
public interface MerchantMemberDao extends JpaRepository<MerchantMember, String>, JpaSpecificationExecutor<MerchantMember> {
    /**
     * 通过store_id查找店铺今日会员
     *
     * @param storeId
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.merchant_member where store_id=?  and TO_DAYS(NOW()) - TO_DAYS(registered_at) =0 order by registered_at desc", nativeQuery = true)
    public Page<MerchantMember> findMerchantMembersByStoreIdOrderByRegisteredAtDescToday(String storeId, Pageable pageable);

    /**
     * 通过store_id查找店铺昨日会员
     *
     * @param storeId
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.merchant_member where store_id=?  and TO_DAYS(NOW()) - TO_DAYS(registered_at) =1 order by registered_at desc", nativeQuery = true)
    public Page<MerchantMember> findMerchantMembersByStoreIdOrderByRegisteredAtDescYesterday(String storeId, Pageable pageable);


    /**
     * 通过store_id查找店铺最近7日会员
     *
     * @param storeId
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.merchant_member where store_id=?  and DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(registered_at) order by registered_at desc", nativeQuery = true)
    public Page<MerchantMember> findMerchantMembersByStoreIdOrderByRegisteredAtDescWeek(String storeId, Pageable pageable);


    /**
     * 通过store_id查找店铺的总会员
     *
     * @param storeId
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.merchant_member where store_id=?  order by registered_at desc", nativeQuery = true)
    public Page<MerchantMember> findMerchantMembersByStoreIdOrderByRegisteredAtDescTotal(String storeId, Pageable pageable);

    /**
     * 通过storeId和memberId查找代理商名下的某一会员
     *
     * @param storeId
     * @param memberId
     * @return
     */
    public MerchantMember findByStoreIdAndMemberId(String storeId, String memberId);
}
